package com.dubbo.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;  
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Distrmysql {

	private final static Logger log = LoggerFactory.getLogger(Distrmysql.class);

	static DbcpConPool db = new DbcpConPool(
			"jdbc:mysql://172.19.0.13:3306/userquery");

	public static String get(String userid) {// 查询过程
		Connection con = null;
		try {
			con = db.getConn();
		} catch (Exception e) {
			return e.toString();
		}
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(String
					.format("select * from %s where ID='%s'",
							tablename(userid), userid));
			java.sql.ResultSetMetaData rsmd = rs.getMetaData();
			rs.next();
			JSONObject jsonObj = new JSONObject();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				String coln = rsmd.getColumnLabel(i);
				String colv = rs.getString(coln);
				jsonObj.put(coln, colv);
			}
			con.close();
			
			return jsonObj.toString();
		} catch (Exception e) {
			log.error("get{}", e.getMessage());
			try {
				con.close();
			} catch (Exception ee) {
			}
			return null;
		}
	}

	public static int update(String userid, String coln, String colv,
			String mdfer) {// 更新过程
		Connection con = null;
		try {
			con = db.getConn();
		} catch (Exception e) {
			log.error("update{}", e.getMessage());
			return -1;
		}
		try {
			Statement stmt = con.createStatement();
DateFormat df=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//设置显示格式
			boolean res = stmt
					.execute(String
							.format("update %s set %s=%s,LastEditTime='%s',Editor='%s' where ID='%s'",
									tablename(userid), coln, colv,
									df.format(new Date(System.currentTimeMillis())),
									mdfer, userid));
			con.close();
			CustomImprove.settodayupdate();
			if (res)
				return 1;
			else
				return 0;
		} catch (Exception e) {
			log.error("update{}", e.getMessage());
			return -1;
		}
	}

	public static int delete(String userid) {// 删除过程
		Connection con = null;
		try {
			con = db.getConn();
		} catch (Exception e) {
			return -1;
		}
		try {
			Statement stmt = con.createStatement();
			stmt.execute(String.format("delete from %s where ID='%s'",
					tablename(userid), userid));
			con.close();
			return 0;
		} catch (Exception e) {
			log.error("delete{}", e.getMessage());
			try {
				con.close();
			} catch (Exception ee) {
			}
			;
			return -1;
		}
	}
	
	public static int dateselect(String sql) {// 任意日期查询
		Connection con = null;
		try {
			con = db.getConn();
		} catch (Exception e) {
			return -1;
		}
		try {
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			rs.next();
			int resualt=rs.getInt("rowcount");
			con.close();
			return resualt;
		} catch (Exception e) {
			log.error("dateselect{}", e.getMessage());
			try {
				con.close();
			} catch (Exception ee) {
			}
			;
			return -1;
		}
	}

	public static int insert(String userid, String Creator, String Status,
			String authentype, String source, String userlevel) {// 更新过程,返回值-1为错误，0为成功
		Connection con = null;
		try {
			con = db.getConn();
		} catch (Exception e) {
			return -1;
		}
		try {
			Statement stmt = con.createStatement();
			stmt.execute(String
					.format("insert into %s(ID,Creator,Status,authentype,source,userlevel) values ('%s','%s',%s,%s,%s,%s)",
							tablename(userid), userid, Creator, Status,
							authentype, source, userlevel));
			con.close();
			CustomImprove.settodayinsert();
			return 0;
		} catch (Exception e) {
			try {
				con.close();
			} catch (Exception ee) {
			}
			;
			return -1;
		}
	}
	
	private static String tablename(String userid) {//根据规则生成要取的表名
		long tableid = Long.parseLong(userid) % 32;
		return "users_" + tableid;
	}

	
	private static int batch(int count) {// 批量处理过程
		Connection con = null;
		try {
			con = db.getConn();
			con.setAutoCommit(false);
		} catch (Exception e) {
			return -1;
		}
		try {
			Statement ps = con.createStatement();
			for (long i = 0; i < count; i++) {
				long k = 700000000 + i;
				String sql = String
						.format("insert into %s(ID,Creator,Status,authentype,source,userlevel) values ('%s','%s',%s,%s,%s,%s)",
								tablename("17"+k), "17"+k, "batch", 1,
								1, 1, 1);
				// 添加进批
				ps.addBatch(sql);
				// 每1000条执行一次
				if (i % 100 == 0) {
					ps.executeBatch();
					ps.clearBatch();
					System.out.println("100条完成"+"-"+"15"+k);
				}
			}
			ps.executeBatch();
			con.close();
			return 0;
		} catch (Exception e) {
			System.out.println(e.toString());
			try {
				con.close();
			} catch (Exception ee) {
			}
			;
			return -1;
		}
	}
	
	 //每次修改记录log入库
	 private static void updatelog(String Editor,String Time)
	 {
		 String sql=String.format("insert into updatelog(Editor,EditTime) values ('%s','%s')",Editor,Time);
		 Connection con = null;
			try {
				con = db.getConn();
			} catch (Exception e) {
				return;
			}
			try {
				Statement stmt = con.createStatement();
				stmt.execute(sql);
				con.close();
				return;
			} catch (Exception e) {
				try {
					con.close();
				} catch (Exception ee) {
				}
				return;
			}
	 }
	 


}
